package com.zbvc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.zbvc.dao.BookDao;
import com.zbvc.entity.Admin;
import com.zbvc.entity.Book;
import com.zbvc.entity.History;
import com.zbvc.util.DBUtil;

/**
 * 图书业务管理
 * 
 * @author Administrator
 *
 */
public class BookDaoImpl implements BookDao {
	/**
	 * 查询所有图书信息
	 */
	@Override
	public List<Book> findAllBooks() {
		String sql = "SELECT * FROM book";
		Connection conn = DBUtil.getConnectDb();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Book> list = new ArrayList<Book>();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();

			while (rs.next()) {
				Book book = new Book();
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setCard(rs.getString("card"));
				book.setAuthor(rs.getString("author"));
				book.setNum(rs.getInt("num"));
				book.setPress(rs.getString("press"));
				book.setType(rs.getString("type"));
				list.add(book);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.CloseDB(rs, pstm, conn);
		}
		return null;
	}

	// 按照书名模糊查询
	@Override
	public List<Book> findLikeBooks(String name) {
		String sql = "SELECT * FROM book WHERE name LIKE '%" + name + "%' ";
		Connection conn = DBUtil.getConnectDb();
		Statement stm = null;
		ResultSet rs = null;
		List<Book> list = new ArrayList<Book>();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				Book book = new Book();
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setCard(rs.getString("card"));
				book.setAuthor(rs.getString("author"));
				book.setNum(rs.getInt("num"));
				book.setPress(rs.getString("press"));
				book.setType(rs.getString("type"));
				list.add(book);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// 增加借阅信息
	@Override
	public int borrowBook(int bid, Admin admin) {
		// 通过bid获取当前图书信息
		Book book = findBookById(bid);
		String sql = "INSERT INTO history (aid,bid,card,bookname,adminname,username,begintime,endtime,status) VALUES "
				+ "(?,?,?,?,?,?,?,?,?)";
		Connection conn = DBUtil.getConnectDb();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		// 生成借阅时间和借阅到期时间
		Calendar cal = Calendar.getInstance();
		int year = cal.get(Calendar.YEAR);
		int month = cal.get(Calendar.MONTH) + 1; // 月份从0开始
		int day = cal.get(Calendar.DAY_OF_MONTH);
		String begintime = "" + year + "-" + month + "-" + day;
		// 生成借阅到期时间 推迟两个月
		cal.add(month, 2);
		String endtime = "" + cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
		int row = 0;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, admin.getAid());
			pstm.setInt(2, book.getBid());
			pstm.setString(3, book.getCard());
			pstm.setString(4, book.getName());
			pstm.setString(5, admin.getName());
			pstm.setString(6, admin.getUsername());
			pstm.setString(7, begintime);
			pstm.setString(8, endtime);
			pstm.setInt(9, 1);
			row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 0;
	}

	// 根据Id查找书籍信息
	@Override
	public Book findBookById(int bid) {
		String sql = " SELECT * FROM book WHERE bid=? ";
		Connection conn = DBUtil.getConnectDb();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Book book = new Book();
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, bid);
			rs = pstm.executeQuery();
			if (rs.next()) {
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setCard(rs.getString("card"));
				book.setAuthor(rs.getString("author"));
				book.setNum(rs.getInt("num"));
				book.setPress(rs.getString("press"));
				book.setType(rs.getString("type"));
			}
			return book;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.CloseDB(rs, pstm, conn);
		}

		return null;
	}

	// 查询借阅历史
	@Override
	public List<History> findHistoryList(int status, int aid) {
		String sql = "SELECT * FROM history WHERE status=? AND aid=? ";
		Connection conn = DBUtil.getConnectDb();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<History> list = new ArrayList<History>();
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, status);
			pstm.setInt(2, aid);
			rs = pstm.executeQuery();
			while (rs.next()) {
				History history = new History();
				history.setHid(rs.getInt("hid"));
				history.setAid(rs.getInt("aid"));
				history.setBid(rs.getInt("bid"));
				history.setBookname(rs.getString("bookname"));
				history.setCard(rs.getString("card"));
				history.setAdminname(rs.getString("adminname"));
				history.setUsername(rs.getString("username"));
				history.setBegintime(rs.getString("begintime"));
				history.setEndtime(rs.getString("endtime"));
				history.setStatus(rs.getInt("status"));
				list.add(history);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.CloseDB(rs, pstm, conn);
		}

		return null;
	}
	//还书
	@Override
	public int backBook(int hid) {

		String sql = "UPDATE history SET endtime=?,status=? WHERE hid=? ";
		Connection conn = DBUtil.getConnectDb();
		PreparedStatement pstm = null;
		// 生成日期
		Calendar c = Calendar.getInstance();
		int year = c.get(Calendar.YEAR);
		int month = c.get(Calendar.MONTH);
		month++;
		int day = c.get(Calendar.DATE);
		// 生成还书日期
		String endtime = "" + year + "-" + month + "-" + day;
		int row = 0;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, endtime);
			pstm.setInt(2, 0); // 0 
			pstm.setInt(3, hid);
			row = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	@Override
	public int insertBook(Book book) {
		String sql = "INSERT INTO book (name,card,author,num,press,type) VALUES (?,?,?,?,?,?) ";
		Connection conn = DBUtil.getConnectDb();
		PreparedStatement pstm = null;
		int row = 0;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(2, book.getCard());
			pstm.setString(1, book.getName());
			pstm.setString(3, book.getAuthor());
			pstm.setString(5, book.getPress());
			pstm.setInt(4, book.getNum());
			pstm.setString(6, book.getType());
			row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

}
